import sys
from PyQt5 import QtCore,  QtWidgets,QtSql
from PyQt5.QtCore import Qt
from ui_calc import Ui_Form
import copy
import os
import traceback
import logging
import re
from openpyxl import load_workbook
class TodoModel(QtCore.QAbstractListModel):
    def __init__(self, todos):
        super().__init__()
        self.todos = copy.copy(todos)

    def data(self, index, role):
        if role == Qt.DisplayRole:
            text = self.todos[index.row()]
            return text

    def rowCount(self, index):
        return len(self.todos)


class Dlgcalc(QtWidgets.QDialog):
    ui = Ui_Form()

    def __init__(self, parent=None):
        super().__init__( parent)
        self.ui.setupUi(self)
        # self.setWindowTitle("方法编号")
        self.model = TodoModel([])
        self.ui.todoView.setModel(self.model)
        self.ui.addButton.pressed.connect(self.add)
        self.ui.deleteButton.pressed.connect(self.delete)
        # self.ui.completeButton.pressed.connect(self.complete)
        self.ui.btn_ok.clicked.connect(self.ok)
        self.ui.btn_cancel.clicked.connect(self.cancel)
        self.ui.pushButton_import_std.clicked.connect(self.import_std_result)
        self.q=QtSql.QSqlQuery()
    def clearResult(self):
        cmd = """drop TABLE STD_Result"""
        logging.info(cmd)
        self.q.prepare(cmd)
        r = self.q.exec_()
        if not r:
            e = self.q.lastError()
            logging.info(e.text())
        self.q.finish()
    def import_std_result(self):
        self.clearResult()
        FileDialog =QtWidgets.QFileDialog(self)
        folder=os.path.join(os.curdir)#os.path.expanduser('~'), "Desktop")
        fil = "xlsx Files (*.xlsx);;All Files (*)"
        FileDirectory = FileDialog.getOpenFileName(
            self, 'select import file', folder, fil)  # 选择目录，返回选中的路径
        fname = FileDirectory[0]
        if fname == "":
            pass
        else:
            try:
                self.import_002(fname)
            except:
                traceback.print_exc()
    def elementWave(self, s):
        wave = re.search(r"\d+(\.\d+)?", s).group()
        # element=re.match("[A-Z][a-z]*",s).group()
        element = s.split(" ")[0]
        return (element, wave)
    def import_002(self, fname):
        xlBook = load_workbook(filename=fname)
        table = xlBook.worksheets[0]
        logging.info(dir(table))
        create_table = False
        data = []
        for row in table.values:
            r = []
            for one in row:
                if one is not None:
                    r.append(one)
                else:
                    r.append(None)
            data.append(r)
        data=data[2:]
        create_table = False
        samples = []
        at = 1
        # print(data)
        sample = {"items": [], "conc": []}
        sample["mass"] = "0"
        sample["volume"] = "0"
        sample["items"] = data[0][1:]
        while True:
            try:
                sampleid = data[at][0]
                sample["sampleid"] = data[at][0]
                sample["conc"] = []
                for one in data[at][1:]:
                    sample["conc"].append(one)  # remove %
                logging.info(sampleid)
            except IndexError:
                break
            if not create_table:
                self.cTable(sample)
                create_table = True
            self.iTable(sample)
            if sampleid == "":
                break
            samples.append(sample)
            at += 1
        logging.info(samples)
        logging.info(len(samples))
    def import_file_std_result(self,fname):
        xlBook = load_workbook(filename=fname)
        table = xlBook.worksheets[0]
        logging.info(dir(table))
        create_table = False
        data = []
        for row in table.values:
            r = []
            for one in row:
                if one is not None:
                    r.append(one)
                else:
                    r.append(None)
            data.append(r)
        samples = []
        at = 2
        while True:
            sample = {"items": [], "conc": []}
            try:
                sampleid = data[at + 1][0]
                sample["sampleid"] = sampleid

                logging.info(sampleid)
            except IndexError:
                break
            col = 1
            while True:
                try:
                    # logging.info(at+2,col)
                    item = data[at + 2][col]
                    conc = data[at + 3][col]
                    # should replace blank to other char
                    sample["items"].append((item, ""))  # .append(item + "_")
                    sample["conc"].append(conc)
                    sample["mass"] = ""
                    sample["volume"] = ""
                except IndexError:
                    break
                if (item == ""):
                    break
                col += 1
            if not create_table:
                self.cTable(sample)
                create_table = True
            self.iTable(sample)
            if sampleid == "":
                break
            samples.append(sample)
            at += 1
        logging.info(samples)
        logging.info(len(samples))

    def cTable(self, sample):
        self.fields = [{"ele": None, "name": "id", "title": "id", "visible": False
                        }, {"ele": None, "name": "sampleid", "title": "样品编号", "visible": True
                            }, {"ele": None, "name": "beizhu", "title": "备注", "visible": True
                                }, {"ele": None, "name": "mass", "title": "质量", "visible": True
                                    }, {"ele": None, "name": "volume", "title": "体积", "visible": True
                                        }, {"ele": None, "name": "xishi", "title": "稀释倍数", "visible": True
                                            }]
        self.eleColumns = len(self.fields)
        for item in sample["items"]:
            logging.info(item)
            title = item#[0]+" "+item[1]
            # to valid column name
            name = item.replace(".", "Q")
            self.fields.append(
                {"ele": item, "name": name, "title": title, "visible": True})
        # if config_json.setting["sblx"] in ["ajl_csv","icap"]:
        #     self.sortFields()
        cmd = """CREATE TABLE STD_Result (
id       INTEGER  PRIMARY KEY AUTOINCREMENT
                    NOT NULL,
sampleid varchr,
beizhu  varchr,
mass    varchr,
volume   varchr,
xishi varchr,
%s)""" % self.genSqlArrDeclare_new()
        logging.info(cmd)
        self.q.prepare(cmd)
        r = self.q.exec_()
        if not r:
            e = self.q.lastError()
            logging.info(e)
        self.q.finish()
        self.sqlArrName = self.gensqlArrName()

    def iTable(self, sample):
        logging.info(sample)
        xishi=sample.get("xishi","")
        cmd = """insert into STD_Result (
sampleid,mass,volume,xishi,%s) values(%s,%s,%s,%s,%s)""" % (
            self.sqlArrName, "'" +
            str(sample["sampleid"]) + "'", "'" + sample["mass"] + "'",
            "'" + str(sample["volume"]) + "'","'" + str(xishi)+ "'", self.genSqlArr(sample["conc"]))
        logging.info(cmd)
        self.q.prepare(cmd)
        r = self.q.exec_()
        if not r:
            e = self.q.lastError()
            logging.info(e)
        self.q.finish()
    def genSqlArr(self, arr):
        r = []
        for one in arr:
            r.append("'" + str(one) + "'")
        return ",".join(r)        
    def genSqlArrDeclare_new(self):
        r = []
        for one in self.fields:
            if one.get("ele") is None:
                pass
            else:
                r.append(one["name"] + " varchr")
        return ",".join(r)

    def gensqlArrName(self):
        r = []
        for one in self.fields:
            if one.get("ele") is None:
                pass
            else:
                r.append("'" + one["name"] + "'")
        return ",".join(r)        
    def ok(self):
        self.accept()

    def cancel(self):
        self.reject()

    def add(self):
        """
        Add an item to our todo list, getting the text from the QLineEdit .todoEdit
        and then clearing it.
        """
        text = self.ui.todoEdit.text()
        # if text:  # Don't add empty strings.
        # Access the list via the model.
        self.model.todos.append(text)
        # Trigger refresh.        
        self.model.layoutChanged.emit()
        #  Empty the input
        self.ui.todoEdit.setText("")
        self.save()

    def delete(self):
        indexes = self.ui.todoView.selectedIndexes()
        if indexes:
            # Indexes is a list of a single item in single-select mode.
            index = indexes[0]
            # Remove the item and refresh.
            del self.model.todos[index.row()]
            self.model.layoutChanged.emit()
            # Clear the selection (as it is no longer valid).
            self.ui.todoView.clearSelection()
            self.save()

    def save(self):
        pass


if __name__ == "__main__":
    app = QtWidgets.QApplication(sys.argv)
    window = Dlgcalc()
    window.show()
    app.exec()
